﻿'Description:批次工序计划类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:YangJiang
'create Date:2018-3-27
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Data.OracleClient
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text

Public Class uLMSContainerWorkFlowStepBusiness
    Implements uLMSContainerWorkFlowStepIBusiness

    ''' <summary>
    ''' 插入批次工序计划数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>Modify zhunan 20181030 PlanType
    Public Function InsertContainerStepPlan(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.InsertContainerStepPlan

        Dim sb = New StringBuilder()
        sb.AppendFormat("SELECT * FROM containerstepplan csp WHERE csp.containerid='{0}' and csp.workflowstepid = '{1}'", para("ContainerID"), para("WorkflowStepID"))

        Dim planType = "1"
        If para("PlanType") <> "1" Then
            planType = "2"
            ' sb.Append(" AND csp.plantype = '2'")
        Else
            'sb.Append(" AND csp.plantype = '1'")
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())

        Dim strSQL As String
        If dt.Rows.Count > 0 Then

            If para("PlanType") = "3" Then
                '【热表转工排产】没有维护或已维护但已解锁
                If para("IsShipLocked") = "0" Then
                    strSQL = <String>
                                            update ContainerStepPlan set PlannedFinishDate=:PlannedFinishDate,isshiplocked=:isshiplocked,
                                                                         stepplanshipdate=:createdate,stepplanshipemployeeid=:createemployeeid,PlanType=:PlanType
                                            where Containerid=:Containerid AND WorkflowStepID=:WorkflowStepID  
                            </String>
                    Dim insertParam(6) As OracleParameter

                    If para("PlannedFinishDate") <> "" Then
                        insertParam(0) = DatabaseHelper.NewParameter(":PlannedFinishDate", para("PlannedFinishDate"), OracleType.DateTime)
                    Else
                        insertParam(0) = DatabaseHelper.NewParameter(":PlannedFinishDate", DBNull.Value, OracleType.DateTime)
                    End If

                    insertParam(1) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
                    insertParam(2) = DatabaseHelper.NewParameter(":WorkflowStepID", para("WorkflowStepID"), OracleType.VarChar)

                    insertParam(3) = DatabaseHelper.NewParameter(":PlanType", planType, OracleType.VarChar)

                    insertParam(4) = DatabaseHelper.NewParameter(":isshiplocked", 1, OracleType.Number)
                    insertParam(5) = DatabaseHelper.NewParameter(":createdate", para("createdate"), OracleType.DateTime)
                    insertParam(6) = DatabaseHelper.NewParameter(":createemployeeid", para("createemployeeid"), OracleType.VarChar)

                    If DatabaseHelper.ExecuteSql(strSQL.ToString, insertParam) > 0 Then
                        InsertContainerStepPlan = True
                    Else
                        InsertContainerStepPlan = False
                    End If
                Else '【热表转工排产】已锁定
                    InsertContainerStepPlan = True
                End If

            Else
                '【非热表转工排产】没有维护或已维护但已解锁
                If para("IsLocked") = "0" Then

                    If para("PlanType") = "2" Then
                        strSQL = <String>
                                            update ContainerStepPlan set PlannedStartDate=:PlannedStartDate,islocked=:islocked,
                                                                         stepplancreatedate=:createdate,stepplancreateemployeeid=:createemployeeid,PlanType=:PlanType
                                            where Containerid=:Containerid AND WorkflowStepID=:WorkflowStepID  
                            </String>
                        Dim insertParam(6) As OracleParameter

                        If para("PlannedStartDate") = "" Then
                            insertParam(0) = DatabaseHelper.NewParameter(":PlannedStartDate", DBNull.Value, OracleType.DateTime)
                        Else
                            insertParam(0) = DatabaseHelper.NewParameter(":PlannedStartDate", DateTime.Parse(para("PlannedStartDate")), OracleType.DateTime)
                        End If

                        insertParam(1) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
                        insertParam(2) = DatabaseHelper.NewParameter(":WorkflowStepID", para("WorkflowStepID"), OracleType.VarChar)

                        insertParam(3) = DatabaseHelper.NewParameter(":PlanType", planType, OracleType.VarChar)

                        insertParam(4) = DatabaseHelper.NewParameter(":islocked", 1, OracleType.Number)
                        insertParam(5) = DatabaseHelper.NewParameter(":createdate", para("createdate"), OracleType.DateTime)
                        insertParam(6) = DatabaseHelper.NewParameter(":createemployeeid", para("createemployeeid"), OracleType.VarChar)



                        If DatabaseHelper.ExecuteSql(strSQL.ToString, insertParam) > 0 Then
                            InsertContainerStepPlan = True
                        Else
                            InsertContainerStepPlan = False
                        End If
                    Else '工序排产更新
                        strSQL = <String>
                                            update ContainerStepPlan set PlannedFinishDate=:PlannedFinishDate,islocked=:islocked,
                                                                         stepplancreatedate=:createdate,stepplancreateemployeeid=:createemployeeid,PlanType=:PlanType
                                            where Containerid=:Containerid AND WorkflowStepID=:WorkflowStepID  
                            </String>
                        Dim insertParam(6) As OracleParameter

                        If para("PlannedFinishDate") <> "" Then
                            insertParam(0) = DatabaseHelper.NewParameter(":PlannedFinishDate", para("PlannedFinishDate"), OracleType.DateTime)
                        Else
                            insertParam(0) = DatabaseHelper.NewParameter(":PlannedFinishDate", DBNull.Value, OracleType.DateTime)
                        End If

                        insertParam(1) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
                        insertParam(2) = DatabaseHelper.NewParameter(":WorkflowStepID", para("WorkflowStepID"), OracleType.VarChar)

                        insertParam(3) = DatabaseHelper.NewParameter(":PlanType", planType, OracleType.VarChar)

                        insertParam(4) = DatabaseHelper.NewParameter(":islocked", 1, OracleType.Number)
                        insertParam(5) = DatabaseHelper.NewParameter(":createdate", para("createdate"), OracleType.DateTime)
                        insertParam(6) = DatabaseHelper.NewParameter(":createemployeeid", para("createemployeeid"), OracleType.VarChar)

                        If DatabaseHelper.ExecuteSql(strSQL.ToString, insertParam) > 0 Then
                            InsertContainerStepPlan = True
                        Else
                            InsertContainerStepPlan = False
                        End If
                    End If


                Else
                    InsertContainerStepPlan = True
                End If

            End If
        Else
            strSQL = <String>
                        insert into ContainerStepPlan (Containerid,ContainerStepPlanID,workflowstepid,PlannedStartDate,PlannedFinishDate,PlanType,islocked,
                                                       stepplancreatedate,stepplancreateemployeeid,specfactoryid,workflowstepname
                                                      )
                        values(:Containerid,:ContainerStepPlanID,:workflowstepid,:PlannedStartDate,:PlannedFinishDate,:PlanType,:islocked,
                               :stepplancreatedate,:stepplancreateemployeeid,:specFactoryid,:workflowstepname
                              )
                    </String>
            Dim insertParam(10) As OracleParameter
            insertParam(0) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
            insertParam(1) = DatabaseHelper.NewParameter(":ContainerStepPlanID", para("ContainerStepPlanID"), OracleType.VarChar)
            insertParam(2) = DatabaseHelper.NewParameter(":workflowstepid", para("WorkflowStepID"), OracleType.VarChar)
            If para("PlannedStartDate") = "" Then
                insertParam(3) = DatabaseHelper.NewParameter(":PlannedStartDate", DBNull.Value, OracleType.DateTime)
            Else
                insertParam(3) = DatabaseHelper.NewParameter(":PlannedStartDate", DateTime.Parse(para("PlannedStartDate")), OracleType.DateTime)
            End If
            If para("PlannedFinishDate") = "" Then
                insertParam(4) = DatabaseHelper.NewParameter(":PlannedFinishDate", DBNull.Value, OracleType.DateTime)
            Else
                insertParam(4) = DatabaseHelper.NewParameter(":PlannedFinishDate", para("PlannedFinishDate"), OracleType.DateTime)
            End If

            'add zhunan 20181030
            If para("PlanType") = "1" Then
                insertParam(5) = DatabaseHelper.NewParameter(":PlanType", para("PlanType"), OracleType.VarChar)
            Else
                insertParam(5) = DatabaseHelper.NewParameter(":PlanType", "2", OracleType.VarChar)
            End If

            'add zhunan 20181031 
            insertParam(6) = DatabaseHelper.NewParameter(":islocked", 1, OracleType.Number)

            insertParam(7) = DatabaseHelper.NewParameter(":stepplancreatedate", para("createdate"), OracleType.DateTime)
            insertParam(8) = DatabaseHelper.NewParameter(":stepplancreateemployeeid", para("createemployeeid"), OracleType.VarChar)
            'yuxj20221108在插入时增加specfactoryid，workflowstepname
            insertParam(9) = DatabaseHelper.NewParameter(":specfactoryid", para("specfactoryid"), OracleType.VarChar)
            insertParam(10) = DatabaseHelper.NewParameter(":workflowstepname", para("workflowstepname"), OracleType.VarChar)

            If DatabaseHelper.ExecuteSql(strSQL.ToString, insertParam) > 0 Then
                InsertContainerStepPlan = True
            Else
                InsertContainerStepPlan = False
            End If
        End If
    End Function

    ''' <summary>
    ''' 变更排产锁定状态 add zhunan 2018.11.7
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsChangeLockStautsSuccess(ByRef para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.IsChangeLockStautsSuccess
        Dim strSQL As String
        If para("PlanType") <> "3" Then
            strSQL = <String>
                       update ContainerStepPlan set islocked=:islocked where Containerid=:Containerid AND WorkflowStepID=:WorkflowStepID 
                       AND PlanType=:PlanType
                    </String>
            Dim insertParam(3) As OracleParameter

            If para("islocked") = "0" Then
                insertParam(0) = DatabaseHelper.NewParameter(":islocked", 0, OracleType.Number)
            Else
                insertParam(0) = DatabaseHelper.NewParameter(":islocked", 1, OracleType.Number)
            End If

            insertParam(1) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
            insertParam(2) = DatabaseHelper.NewParameter(":WorkflowStepID", para("WorkflowStepID"), OracleType.VarChar)

            If para("PlanType") <> "1" Then
                insertParam(3) = DatabaseHelper.NewParameter(":PlanType", "2", OracleType.VarChar)
            Else
                insertParam(3) = DatabaseHelper.NewParameter(":PlanType", "1", OracleType.VarChar)
            End If

            If DatabaseHelper.ExecuteSql(strSQL.ToString, insertParam) > 0 Then
                IsChangeLockStautsSuccess = True
            Else
                IsChangeLockStautsSuccess = False
            End If
        Else
            strSQL = <String>
                       update ContainerStepPlan set isshiplocked=:isshiplocked where Containerid=:Containerid AND WorkflowStepID=:WorkflowStepID 
                       AND PlanType=:PlanType
                    </String>

            Dim insertParam(3) As OracleParameter

            If para("isshiplocked") = "0" Then
                insertParam(0) = DatabaseHelper.NewParameter(":isshiplocked", 0, OracleType.Number)
            Else
                insertParam(0) = DatabaseHelper.NewParameter(":isshiplocked", 1, OracleType.Number)
            End If

            insertParam(1) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
            insertParam(2) = DatabaseHelper.NewParameter(":WorkflowStepID", para("WorkflowStepID"), OracleType.VarChar)

            If para("PlanType") <> "1" Then
                insertParam(3) = DatabaseHelper.NewParameter(":PlanType", "2", OracleType.VarChar)
            Else
                insertParam(3) = DatabaseHelper.NewParameter(":PlanType", "1", OracleType.VarChar)
            End If

            If DatabaseHelper.ExecuteSql(strSQL.ToString, insertParam) > 0 Then
                IsChangeLockStautsSuccess = True
            Else
                IsChangeLockStautsSuccess = False
            End If
        End If
        Return IsChangeLockStautsSuccess
    End Function

    ''' <summary>
    ''' 更新批次工序计划数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerStepPlan(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.UpdateContainerStepPlan
        Dim strSQL As String =
            <String>
                update ContainerStepPlan set Containerid=:Containerid,workflowstepid=:workflowstepid,PlannedStartDate=:PlannedStartDate,
                PlannedFinishDate=:PlannedFinishDate where ContainerStepPlanID=:ContainerStepPlanID
            </String>
        Dim updateParam(4) As OracleParameter
        updateParam(0) = DatabaseHelper.NewParameter(":Containerid", para("ContainerID"), OracleType.VarChar)
        updateParam(1) = DatabaseHelper.NewParameter(":ContainerStepPlanID", para("ContainerStepPlanID"), OracleType.VarChar)
        updateParam(2) = DatabaseHelper.NewParameter(":workflowstepid", para("WorkflowStepID"), OracleType.VarChar)
        updateParam(3) = DatabaseHelper.NewParameter(":PlannedStartDate", para("PlannedStartDate"), OracleType.DateTime)
        updateParam(4) = DatabaseHelper.NewParameter(":PlannedFinishDate", para("PlannedFinishDate"), OracleType.DateTime)
        If DatabaseHelper.ExecuteSql(strSQL.ToString, updateParam) > 0 Then
            UpdateContainerStepPlan = True
        Else
            UpdateContainerStepPlan = False
        End If
    End Function

    ''' <summary>
    ''' 排产批次查询 add zhunan 2018.11.6
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerStepDispatchData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.GetContainerStepDispatchData
        Dim sb = New StringBuilder()
        Dim str_value As New StringBuilder
        Dim str2_value As New StringBuilder
        sb.AppendLine("SELECT  DISTINCT cci.togetherflag,cci.productname,cci.containerno,cci.workflowroute,cci.mfgordername,cci.planqty,cci.workflowstepname,cci.workflowstepid,cci.containertype,")
        sb.AppendLine("DECODE(cci.containertype, 2,'批量加工',3,'批量带子序号',7,'单件加工') AS containertypename,cci.plannedstartdate,cci.plannedcompletiondate,cci.qty ")
        str_value.AppendLine(" FROM containercurrentinfo cci")
        'str_value.AppendLine(" LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid --AND ws.sequence>=cci.workflowstepsequence")
        str_value.AppendLine(" LEFT JOIN container c ON cci.containerid = c.containerid")
        str_value.AppendLine(" LEFT JOIN Workflowstep ws ON ws.workflowid = DECODE(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)")
        str_value.AppendLine(" LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid")
        str_value.AppendLine(" LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)")

        str_value.AppendLine(" WHERE cci.containerstatus = 1 AND (cci.isinstore = 0 OR cci.isinstore = -1) AND cci.splitnum = 0 ")
        sb.AppendLine(str_value.ToString())
        sb.AppendLine("AND cci.containertype <> '7' ")
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "2" Then '已维护
                If QueryCondition.ContainsKey("Type") Then
                    If QueryCondition("Type") = "1" Then '工序排产&已维护
                        str2_value.AppendLine(" AND cci.originalfactoryid = s.factoryid and  EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid AND csp.plantype = '1' AND ROWNUM=1 ) ")
                    End If
                    If QueryCondition("Type") = "2" Then '转工排产&已维护
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid and EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' AND ROWNUM=1 ) ")
                    End If
                    If QueryCondition("Type") = "3" Then '热表排产&已维护
                        str2_value.AppendFormat(" and s.factoryid='{0}' ", QueryCondition("FactoryID"))
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid ")
                        str2_value.AppendLine(" AND  EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' and csp.plannedfinishdate is not null AND ROWNUM=1 ) ") '热表已维护
                    End If
                End If
            Else '未维护
                If QueryCondition.ContainsKey("Type") Then
                    If QueryCondition("Type") = "1" Then '工序排产&未维护
                        str2_value.AppendLine(" AND cci.originalfactoryid = s.factoryid and NOT EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid AND csp.plantype = '1' AND ROWNUM=1 ) ")
                    End If
                    If QueryCondition("Type") = "2" Then '转工排产&未维护
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid and NOT EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' AND ROWNUM=1 )")
                    End If
                    If QueryCondition("Type") = "3" Then '热表排产&未维护
                        str2_value.AppendFormat(" and s.factoryid='{0}' ", QueryCondition("FactoryID"))
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid  ")
                        str2_value.AppendLine(" AND  EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' and csp.plannedfinishdate is null AND ROWNUM=1 ) ") '热表未维护
                    End If
                End If
            End If
        End If

        If QueryCondition.ContainsKey("Type") Then
            If QueryCondition("Type") <> "3" Then '工序排产和转工排产只能本分厂可以查看 modify by tianFW 2018年11月14日
                If QueryCondition.ContainsKey("WorkCenterID") Then
                    str2_value.AppendFormat(" AND (cci.fromworkcenterid='{0}' OR cci.stepworkcenterids LIKE '%{0}%')", QueryCondition("WorkCenterID"))
                End If
                str2_value.AppendFormat(" AND cci.originalfactoryid='{0}'", QueryCondition("FactoryID"))
            Else
                str2_value.AppendFormat(" AND cci.originalfactoryid <> '{0}' ", QueryCondition("FactoryID")) '本分厂的不显示转工完成时间信息 add by tianFW 2018年11月14日
            End If
        End If

        If QueryCondition.Keys.Contains("ScanContainerName") Then
            str2_value.AppendFormat(" AND cci.containername ='{0}' ", QueryCondition("ScanContainerName"))
        End If

        If QueryCondition.Keys.Contains("mfgOrderName") Then
            str2_value.AppendFormat(" AND cci.mfgordername LIKE '%{0}%'", QueryCondition("mfgOrderName"))
        End If

        If QueryCondition.Keys.Contains("ContainerName") Then
            str2_value.AppendFormat(" AND cci.containername LIKE '%{0}%' ", QueryCondition("ContainerName"))
        End If

        If QueryCondition.Keys.Contains("ProductName") Then
            str2_value.AppendFormat(" AND cci.productname LIKE'%{0}%'", QueryCondition("ProductName").Trim.ToUpper)
        End If
        sb.AppendLine(str2_value.ToString())
        sb.AppendLine("       UNION ALL")
        sb.AppendLine("       SELECT  cci.togetherflag,cci.productname,cci.containerno,cci.workflowroute,cci.mfgordername,cci.planqty,cci.workflowstepname,cci.workflowstepid,cci.containertype,")
        sb.AppendLine("               DECODE(cci.containertype, 2,'批量加工',3,'批量带子序号',7,'单件加工') AS containertypename,cci.plannedstartdate,cci.plannedcompletiondate,")
        sb.AppendLine("               COUNT(DISTINCT cci.containername) qty ")
        sb.AppendLine(str_value.ToString())
        sb.AppendLine(" AND cci.containertype = '7' ")
        sb.AppendLine(str2_value.ToString())
        sb.AppendLine(" GROUP BY cci.togetherflag,cci.productname,cci.containerno,cci.workflowroute,cci.mfgordername,cci.planqty,cci.workflowstepname,cci.containertype,")
        sb.AppendLine("cci.containertype,cci.plannedstartdate,cci.plannedcompletiondate,cci.workflowstepid")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function


    ''' <summary>
    ''' 创建批次查询数据条件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        'add zhunan 20181030
        If para.Keys.Contains("Type") Then
            If para("Type") <> "3" Then
                sbCondition.AppendFormat(" WHERE (C.FACTORYID='{0}' OR (C.ORIGINALFACTORYID='{0}' AND C.ISSHIP=1))", para("FactoryID"), para("FactoryID")) 'add:Wangjh 2017-10-18 OR (C.ORIGINALFACTORYID='{0}' AND C.ISSHIP=1) )
            Else
                sbCondition.AppendLine(" WHERE 1=1 ") 'add zhunan 20181030
            End If
        End If

        If para.Keys.Contains("TeamID") Then
            sbCondition.AppendFormat(" AND (C.TEAMID='{0}' OR C.STEPTEAMIDS LIKE '%{0}%') ", para("TeamID")) 'ADD:Wangjh 增加stepteamids条件
        End If
        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName").Trim.ToUpper)
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:wangjh 2017-8-8
            sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '%{0}%'", para("MfgOrderName"))
        End If
        GetCondition = sbCondition.ToString
    End Function


    ''' <summary>
    ''' 获取当前批次下当前工艺下的所有工序  
    ''' </summary>
    ''' <param name="ContainerID"></param>
    ''' <returns></returns>
    ''' <remarks>add YangJiang 20180711 ,s.factoryid SpecFactoryid,wf.factoryid WorkFlowFactoryid</remarks>
    Public Function GetWorkFlowStepByWorkFlowID(ByVal ContainerID As String) As System.Data.DataTable Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.GetWorkFlowStepByWorkFlowID
        Dim strSQL As New StringBuilder
        strSQL.AppendLine("SELECT wfs2.workflowstepname,wfs2.workflowstepid,csp.plannedfinishdate,csp.plannedstartdate,s.factoryid SpecFactoryid,wfs2.sequence,")
        strSQL.AppendLine("cu.originalfactoryid WorkFlowFactoryid,wfs2.workcenterid,wfs2.teamid,csp.islocked,wfs2.wipmsglabel,csp.isshiplocked") '新增查询csp.isshiplocked字段 add zhunan 20191.14 
        strSQL.AppendLine("FROM containercurrentinfo cu")
        strSQL.AppendLine("LEFT JOIN container c ON c.containerid = cu.containerid")
        strSQL.AppendLine("LEFT JOIN Workflowstep wfs2 ON wfs2.workflowid= DECODE(cu.isinstore,-1,c.mfgworkflowid,cu.workflowid)")
        'add YangJiang 20180711 获取工序所属工厂及工序所属工艺的所属分厂
        strSQL.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = wfs2.SPECBASEID")
        strSQL.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, wfs2.SPECID)")
        strSQL.AppendLine("LEFT JOIN containerstepplan csp ON csp.containerid=cu.containerid AND csp.workflowstepid=wfs2.workflowstepid")
        strSQL.AppendFormat("WHERE cu.containerid IN ('{0}') and cu.qty > 0 and cu.containerstatus = 1 AND (cu.isinstore = 0 OR cu.isinstore = -1) and wfs2.sequence is not null", ContainerID) 'and wfs2.sequence is not null add by tianFW
        strSQL.AppendLine(" GROUP BY wfs2.workflowstepname,wfs2.workflowstepid,csp.plannedfinishdate,csp.plannedstartdate,s.factoryid,")
        strSQL.AppendLine("cu.originalfactoryid,wfs2.workcenterid,wfs2.teamid,csp.islocked,wfs2.sequence,wfs2.wipmsglabel,csp.isshiplocked")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable("SELECT * FROM (" & strSQL.ToString & ")ORDER BY sequence")
        Return dt
    End Function

    ''' <summary>
    ''' 获取派工要求时间  add YangJiang 20180529
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>add and c.isuse = 0 by tianFW 2018年9月29日</remarks>
    Public Function GetDispatcherDate(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.GetDispatcherDate
        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT c.containerid,c.workflowstepid,MIN(c.plannedstartdate)plannedstartdate,max(c.plannedfinishdate)plannedfinishdate")
        strSQL.AppendLine(" FROM containerstepdispatch c")
        strSQL.AppendFormat(" WHERE c.containerid='{0}' AND c.workflowstepid='{1}' and c.isuse = 0", para("ContainerID"), para("WorkFlowStepID"))
        strSQL.AppendLine(" GROUP BY c.containerid,c.workflowstepid")
        GetDispatcherDate = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)
    End Function

    ''' <summary>
    ''' 根据件号/批次号获取流水卡号 add zhunan 2018.11.5
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainernameByProductBatch(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.GetContainernameByProductBatch
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT DISTINCT cci.workflowroute,cci.productname,cci.containername,cci.containerid,cci.togetherflag,cci.containerno,cci.isinstore,cci.plannedstartdate PlannedStartDate")
        sb.AppendLine("FROM containercurrentinfo cci")
        sb.AppendLine("LEFT JOIN container c ON c.containerid = cci.containerid")
        sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = DECODE(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)")
        sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid")
        sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)")
        sb.AppendLine("WHERE cci.containerstatus = 1 AND (cci.isinstore = 0 OR cci.isinstore = -1) AND cci.splitnum = 0 ")

        If para.ContainsKey("Containerno") Then
            If para("Containerno") <> "" Then
                sb.AppendFormat(" AND cci.containerno = '{0}'", para("Containerno"))
            End If
        End If

        If para.ContainsKey("Productname") Then
            If para("Productname") <> "" Then
                sb.AppendFormat(" AND cci.productname = '{0}'", para("Productname"))
            End If
        End If
        If para.ContainsKey("WorkflowStepID") Then
            sb.AppendFormat(" AND cci.WorkflowStepID='{0}' ", para("WorkflowStepID"))
        End If
        If para.ContainsKey("MfgorderName") Then
            sb.AppendFormat(" AND cci.MfgorderName='{0}' ", para("MfgorderName"))
        End If

        Dim str2_value As New StringBuilder
        If para.Keys.Contains("Status") Then
            If para("Status") = "2" Then '已维护
                If para.ContainsKey("Type") Then
                    If para("Type") = "1" Then '工序排产&已维护
                        str2_value.AppendLine(" AND cci.originalfactoryid = s.factoryid and  EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid AND csp.plantype = '1' AND ROWNUM=1 ) ")
                    End If
                    If para("Type") = "2" Then '转工排产&已维护
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid and EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid AND csp.plantype = '2' AND ROWNUM=1 ) ")
                    End If
                    If para("Type") = "3" Then '热表排产&已维护
                        str2_value.AppendFormat(" and s.factoryid='{0}' ", para("FactoryID"))
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid ")
                        str2_value.AppendLine(" AND  EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' and csp.plannedfinishdate is not null AND ROWNUM=1 ) ") '热表已维护
                    End If
                End If
            Else '未维护
                If para.ContainsKey("Type") Then
                    If para("Type") = "1" Then '工序排产&未维护
                        str2_value.AppendLine(" AND cci.originalfactoryid = s.factoryid and NOT EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid AND csp.plantype = '1' AND ROWNUM=1 ) ")
                    End If
                    If para("Type") = "2" Then '转工排产&未维护
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid and NOT EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' AND ROWNUM=1 )")
                    End If
                    If para("Type") = "3" Then '热表排产&未维护
                        str2_value.AppendFormat(" and s.factoryid='{0}' ", para("FactoryID"))
                        str2_value.AppendLine(" AND cci.originalfactoryid <> s.factoryid  ")
                        str2_value.AppendLine(" AND  EXISTS ( SELECT 1 FROM  containerstepplan csp WHERE csp.containerid=cci.containerid and csp.workflowstepid=ws.workflowstepid  AND csp.plantype = '2' and csp.plannedfinishdate is null AND ROWNUM=1 ) ") '热表未维护
                    End If
                End If
            End If
        End If

        If para.ContainsKey("Type") Then
            If para("Type") <> "3" Then '工序排产和转工排产只能本分厂可以查看 modify by tianFW 2018年11月14日
                If para.ContainsKey("WorkCenterID") Then
                    str2_value.AppendFormat(" AND (cci.fromworkcenterid='{0}' OR cci.stepworkcenterids LIKE '%{0}%')", para("WorkCenterID"))
                End If
                str2_value.AppendFormat(" AND cci.originalfactoryid='{0}'", para("FactoryID"))
            Else
                str2_value.AppendFormat(" AND cci.originalfactoryid <> '{0}' ", para("FactoryID")) '本分厂的不显示转工完成时间信息 add by tianFW 2018年11月14日
            End If
        End If

        sb.AppendLine(str2_value.ToString())

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString & " ORDER BY containername")
        Return dt
    End Function

    ''' <summary>
    ''' 员工是否有权限解锁工序计划排产
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsEmployeeCanUnlockStepPlan(ByRef para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.IsEmployeeCanUnlockStepPlan
        IsEmployeeCanUnlockStepPlan = False
        Dim sb = New StringBuilder()
        sb.AppendLine("SELECT e.employeeid")
        sb.AppendLine("FROM roledef rd")
        sb.AppendLine("LEFT JOIN employeerole er ON er.roleid = rd.roleid")
        sb.AppendLine("LEFT JOIN employee e ON e.employeeid = er.employeeid")
        sb.AppendLine("WHERE rd.rolename = '计划重排'")

        If para.ContainsKey("employeeid") Then
            sb.AppendFormat(" AND e.employeeid = '{0}' ", para("employeeid"))
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        If dt.Rows.Count > 0 Then
            IsEmployeeCanUnlockStepPlan = True
        End If

        Return IsEmployeeCanUnlockStepPlan
    End Function

    Public Function GetContainerStepFinishData(ByVal containerid As String) As DataTable Implements IBusiness.uLMSContainerWorkFlowStepIBusiness.GetContainerStepFinishData
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT t.workflowstepname,t.workflowstepid,NVL(NVL(checkdate,recivedate),indate) finishdate")
        sb.AppendLine("FROM (")
        sb.AppendLine("     SELECT ws.workflowstepname,ws.workflowstepid,csd.usecompleted,MAX(csd.checkdate) checkdate,csr.recivedate,cm.indate")
        sb.AppendLine("     FROM containercurrentinfo cci")
        sb.AppendLine("     LEFT JOIN container c ON cci.containerid = c.containerid")
        sb.AppendLine("     LEFT JOIN workflowstep ws ON ws.workflowid = DECODE(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)")
        sb.AppendLine("     LEFT JOIN containersteprecord csd ON csd.workflowstepid = ws.workflowstepid AND cci.containerid = csd.containerid")
        sb.AppendLine("     LEFT JOIN (")
        sb.AppendLine("                 SELECT csr.recivedate,csrd.workflowstepid,csr.containerid")
        sb.AppendLine("                 FROM containershiprecive csr")
        sb.AppendLine("                 LEFT JOIN containershiprecordstep csrd ON csrd.containershiprecordid = csr.containershiprecordid")
        sb.AppendFormat("                 WHERE csr.containerid='{0}'", containerid)
        sb.AppendLine("               ) csr ON csr.workflowstepid = ws.workflowstepid AND csr.containerid = cci.containerid ")

        sb.AppendLine("     LEFT JOIN (")
        sb.AppendLine("                 SELECT cci.containerid,ws.workflowstepid,cm.indate")
        sb.AppendLine("                 FROM containercurrentinfo cci")
        sb.AppendLine("                 LEFT JOIN container c ON cci.containerid = c.containerid")
        sb.AppendLine("                 LEFT JOIN workflowstep ws ON ws.workflowid = DECODE(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)")
        sb.AppendLine("                 LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid")
        sb.AppendLine("                 LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)")
        sb.AppendLine("                 LEFT JOIN containercommissioning cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid")
        sb.AppendFormat("                 WHERE s.controlcode = 'LY05' AND cci.containerid = '{0}'", containerid)
        sb.AppendLine("               ) cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid")
        sb.AppendFormat("WHERE cci.containerid='{0}'", containerid)
        sb.AppendLine("GROUP BY ws.workflowstepname,ws.workflowstepid,csd.usecompleted,csr.recivedate,cm.indate")
        sb.AppendLine(") T")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        Return dt
    End Function
End Class
